<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class data_model extends MY_Model {
    protected $table = 'data'; 

    public function __construct() {
        parent::__construct();
        $this->db2 = $this->load->database("datasuper",true);
    }

    public function getViewAnalysis($where=false,$step=0,$pid=0) {

        $fields   = array();
        $fields[] = 'sum(vcount) as pv_all';
        $fields[] = 'sum(pnum) as uv_all';
        $fields[] = 'sum(ipcount) as ip_all';
        $fields[] = 'avg(vcount) as pv_avg';
        $fields[] = 'avg(pnum) as uv_avg';
        $fields[] = 'avg(ipcount) as ip_avg';
        $fields[] = 'max(vcount) as pv_max';
        $fields[] = 'max(pnum) as uv_max';
        $fields[] = 'max(ipcount) as ip_max';

        // 判断时间
        $table = 'day_report_'.$pid;
        if (!$step) {
            $time_format = "%Y-%m-%d";
            $fields[] = "DATE_FORMAT(dcode,'$time_format') as date";
        }
        else{
            $table = 'hour_report_'.$pid;
            $time_format = "%Y-%m-%d %H";
            $fields[] = "DATE_FORMAT(dcode,'$time_format') as date";
        }
        
        if (!$this->db2->table_exists($table)) {

            return false;
        }
    	

        $query = $this->db2->select($fields)
    	          ->from($table)
                  ->where($where)
    	          ->group_by(array('date'))
    	          ->get();
        
    	return $query->result_array();
    }

    public function getVistorAnalysis($id) {

        $fields = 'fopenid,count(*)';
        $table = 'm_relation_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }
        $order = array();
    	$query = $this->db2->select($fields)
                  ->from($table)
                  ->order_by('count(*)','desc')
                  ->order_by('dcode','asc')
                  ->group_by('fopenid')
                  ->get();
        $result = $query->result_array();

        $tmp = array();
        $total = 0;
        foreach ($result as $key => $value) {
            $tmp[] = '"'.$value['fopenid'].'"';
            $total += $value['count(*)'];
            if ($total>=200) {
                break;
            }
        }

        $str = implode(",", $tmp);

        $sql = "SELECT (CONCAT(openid,fopenid)) AS ofid,openid,fopenid,acode,fcode,flag,dcode FROM $table WHERE fopenid IN ($str) GROUP BY ofid  ORDER BY FIELD(fopenid,$str) ";
        $query = $this->db2->query($sql);
        return $query->result_array();
    }


    //分享 浏览查询
    public function getUserInfoShareView($user,$id) {
        $table2 = 'm_relation_'.$id;
        if (!$this->db2->table_exists($table2)) {

            return false;
        }
        $fields = 'a.id,a.carea, a.headimgurl,a.nickname,a.sex,COUNT(b.openid) as share,SUM(b.flag) as view ,b.fopenid';
        $table = 'm_user_merge as a';
        $order = array();
        $query = $this->db2->select($fields)
                  ->from($table)
                  ->where_in("a.openid",$user)
                  ->join("$table2 as b",'a.openid = b.fopenid')
                  ->group_by('b.fopenid')
                  ->order_by('view')
                  ->get();

        return $query->result_array();
    }

    //通过给定user数组 获得用户详细信息
    public function getUserInfo($user,$id) {
        $fields = '*';
        $table = 'm_user_merge';
        $query = $this->db2->select($fields)
                  ->from($table)
                  ->where_in("openid",$user)
                  ->get();
        
        return $query->result_array();
    }


    //获取用户性别信息
    public function getUserSex($id) {
        $table = 'm_relation_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }
        $sql = "SELECT COUNT(*) as num,sex 
                FROM m_user_merge 
                WHERE openid IN(
                    SELECT DISTINCT * FROM 
                        (   
                            SELECT  openid FROM $table 
                                UNION 
                            SELECT  fopenid AS openid FROM $table 
                        ) 
                    AS sorttable) 
                   
                
                GROUP BY sex";
        $query = $this->db2->query($sql);
        return $query->result_array();
    }

    public function getUniUser($id){
        $table = 'm_relation_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }
        $sql = "SELECT DISTINCT * FROM (
                        (   
                            SELECT  openid FROM $table 
                                UNION 
                            SELECT  fopenid AS openid FROM $table 
                        ) 
                    AS sorttable) 
                    ORDER BY openid
                ";
        $query = $this->db2->query($sql);
        return $query->result_array();
    }

    public function getUserCityReport($id){
        $table = 'm_relation_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }
        $sql = "SELECT COUNT(*) AS num , a.`acode`,b.level,b.title
                FROM $table AS a 
                LEFT JOIN m_province_city AS b ON a.acode = b.code
                WHERE  a.`acode` IS NOT NULL 
                GROUP BY a.acode 
                ";
        $query = $this->db2->query($sql);
        return $query->result_array();
    }

    public function get_step_list($pid){
        $table = "relation_".$pid;

        if (!$this->db2->table_exists($table)) {

            return false;
        }

        $query = $this->db2->select("count(1) as num,fcode")
                  ->from($table)
                  ->group_by('fcode')
                  ->order_by("num",'desc')
                  ->get();
        $result = $query->result_array();
        return $result;
    }


    public function getUserHabit($pid){

        $table = 'hour_report_'.$pid;
        if (!$this->db2->table_exists($table)) {

            return false;
        }

        $query = $this->db2->select("*,sum(vcount) as num,hour(dcode) as hour")
                  ->from($table)
                  ->order_by("hour")
                  ->group_by("hour")
                  ->get();
        $result = $query->result_array();
        return $result;
    }

    public function getUserChannel($pid){

        $table = 'day_report_'.$pid;
        if (!$this->db2->table_exists($table)) {

            return false;
        }

        $query = $this->db2->select("SUM(pnum) as num,chcode")
                  ->from($table)
                  ->group_by("chcode")
                  ->get();
        $result = $query->result_array();
        return $result;
    }

    public function getUserSource($pid){

        $table = 'day_report_'.$pid;
        if (!$this->db2->table_exists($table)) {

            return false;
        }

        $query = $this->db2->select("SUM(pnum) as num,source")
                  ->from($table)
                  ->where(array('chcode'=>1))
                  ->group_by("source")
                  ->get();
        $result = $query->result_array();
        return $result;
    }

    public function getUserSpreadShare($id,$step=0,$where="",$field=false,$order=false){


        $table = 'm_relation_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }


        $time_format = "%Y-%m-%d";

        if ($step) {

            $time_format = "%Y-%m-%d %H";
        }

        $tmp = array("dcode <=","dcode >=");

        $time_arr = array();

        foreach ($where as $key => $value) {

            if (in_array($key, $tmp)) {

                $time_arr[] = $key." '".$value."' ";
            }
        }

        $time_sql = implode(" AND ", $time_arr);

        if (!$field) {

            $field = 'a.shared_num ,a.fopenid,b.total_num';
        }

        $sql = "SELECT $field
                     FROM 
                        (SELECT COUNT(1) AS shared_num,fopenid FROM $table WHERE flag >0 AND $time_sql GROUP BY fopenid) 
                        AS a
                    LEFT JOIN
                        (SELECT COUNT(1) AS total_num,fopenid AS bf FROM $table WHERE  $time_sql GROUP BY fopenid) 
                        AS b 
                        ON b.bf = a.fopenid 
                    GROUP BY fopenid";

        if ($order) {

            $sql .= ' order by '.implode(' ', $order);
        }

        $query = $this->db2->query($sql);

        return $query->result_array();
    }

    // 获取活动的总数据
    public function getViewP( $project_id=false, $channel_id=false, $fields=false ) {

        if (!$fields) {

            $fields   = array();
            $fields[] = 'sum(vcount) as click,sum(scount) as share ,sum(pnum) as pnum';
            $fields[] = '(UNIX_TIMESTAMP(max(dcode)) - unix_timestamp(min(dcode))) as time';
        }
        
        $where = array();
        if ( !$project_id ) {
            
            return false;
        } else {

            $table = 'day_report_'.$project_id;
            if (!$this->db2->table_exists($table)) {

                return false;
            }
        }

        if ( $channel_id ) {
            
            $where['chcode'] = $channel_id;
        }
        $query = $this->db2->select($fields)
                           ->from($table)
                           ->where($where)
                           ->get();
        return $query->row_array();

    }

    // 获取活动的总数据
    public function getChannelView( $project_id=false, $channel_ids=false, $group_by=false, $fields=false ) {

        if (!$fields) {

            $fields   = array();
            $fields[] = 'sum(vcount) as click,sum(scount) as share ,sum(pnum) as pnum';
            $fields[] = 'dcode as time';

            if ($group_by == 'chcode') {
                
                $fields[] = 'chcode';
            }
        }
        
        $where = array();
        if ( !$project_id ) {
            
            return false;
        } else {

            $table = 'day_report_'.$project_id;
            if (!$this->db2->table_exists($table)) {

                return false;
            }
        }

        if ( $channel_ids and is_array($channel_ids) ) {
            
            $where['in'] = array('chcode', $channel_ids);
        }
        
        $this->db2->select($fields)
                  ->from($table)
                  ->where($where);

        if ( $group_by ) {
            
            $this->db2->group_by($group_by);
        }
        $query = $this->db2->get();

        return $query->result_array();

    }

    public function getUserRelationByDate($id,$step=0,$where=""){

        $table = 'm_relation_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }

        $time_format = "%Y-%m-%d";

        if ($step) {

            $time_format = "%Y-%m-%d %H";
        }

        $tmp = array("dcode <=","dcode >=");
        $time_arr = array();

        foreach ($where as $key => $value) {

            if (in_array($key, $tmp)) {

                $time_arr[] = $key." '".$value."' ";
            }
        }
        
        $time_sql = implode(" AND ", $time_arr);

        $sql = "SELECT a.shared_num ,b.total_num,a.adate 
                    AS date FROM 
                        (SELECT COUNT(1) AS shared_num ,DATE_FORMAT(dcode,'$time_format') AS adate FROM $table WHERE flag >0 AND $time_sql GROUP BY  adate) 
                        AS a
                    LEFT JOIN
                        (SELECT COUNT(1) AS total_num ,DATE_FORMAT(dcode,'$time_format') AS bdate  FROM $table WHERE $time_sql GROUP BY bdate) 
                        AS b 
                        ON  b.bdate = a.adate
                    GROUP BY date";

        $query = $this->db2->query($sql);
        return $query->result_array();
    }

    public function pageClickTrans($where=false){

        if (!$where) {

            return false;
        }

        $table = 'button_report';
        $query = $this->db2->select("sum(vcount) as click,bname as name")
                  ->from($table)
                  ->where($where)
                  ->order_by("click")
                  ->group_by("name")
                  ->get();

        $result = $query->result_array();
        return $result;
    }


    public function pageLossRate($id){

        $table = 'page_report';
        $query = $this->db2->select("pindex,url,sum(v) as view,sum(time) as viewtime,sum(u) as user")
                  ->from($table)
                  ->where(array("wid"=>$id))
                  ->order_by("pindex")
                  ->group_by("pindex")
                  ->get();

        $result = $query->result_array();
        return $result;

    }

    public function timeSegment($id){

        $table = 'ptlevel_report';
        $query = $this->db2->select("name,sum(v) as value")
                  ->from($table)
                  ->where(array("wid"=>$id))
                  ->order_by("tlevel","asc")
                  ->group_by("tlevel")
                  ->get();

        $result = $query->result_array();
        return $result;

    }


    public function areaMigration($id,$where=""){

        $table = 'm_relation_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }

        $tmp = array("dcode <=","dcode >=");
        $time_arr = array();

        foreach ($where as $key => $value) {

            if (in_array($key, $tmp)) {

                $time_arr[] = $key." '".$value."' ";
            }
        }
        
        $time_sql = implode(" AND ", $time_arr);

        $sql = " SELECT COUNT(*) as num ,a.`openid`,DATE_FORMAT(a.dcode,'%Y-%m-%d') AS date, a.`acode` AS child_city,b.acode AS father_city 
                FROM $table AS a 
                LEFT  JOIN ((SELECT acode,openid FROM $table where $time_sql GROUP BY openid) AS b)
                    ON a.`fopenid` = b.openid
                WHERE fopenid != 'root' AND a.`acode` IS NOT NULL AND b.acode IS NOT NULL and  $time_sql
                GROUP BY date,father_city ,child_city ORDER BY num desc";

        $query = $this->db2->query($sql);

        return $query->result_array();        
        
    }

    public function areaView($id,$where=false){

        $table = 'm_relation_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }

        $tmp = array("dcode <=","dcode >=");
        $time_arr = array();
        if (is_array($where)) {
            foreach ($where as $key => $value) {

                if (in_array($key, $tmp)) {

                    $time_arr[] = $key." '".$value."' ";
                }
            }
        }
        
        
        $time_sql = implode(" AND ", $time_arr);

        $sql = " SELECT COUNT(*) as num , `acode`,DATE_FORMAT(dcode,'%Y-%m-%d') AS date
                FROM $table 
                WHERE `acode` IS NOT NULL ";

        if ($time_sql) {
            $sql .= "and $time_sql";
        }
        
                 
        $sql .=  " GROUP BY acode,date order by date asc,num desc";

        $query = $this->db2->query($sql);

        return $query->result_array();        
        
    }

    public function getAllCityName(){

        $table = 'province_city';
        $query = $this->db2->select("code,title")
                  ->from($table)
                  ->where(array("code >"=>0))
                  ->get();

        $result = $query->result_array();
        return $result;
    }

    public function getAllCityProvinceName(){

        $table = 'province_city';
        $query = $this->db2->select("province_city.code,province.title")
                  ->from($table)
                  ->join("province","province_city.province_id = province.id")
                  ->where(array("province_city.code >"=>0))
                  ->get();

        $result = $query->result_array();
        return $result;
    }

    public function terminalAnalysis($id){

        $table = 'dv_report';
        $query = $this->db2->select("type,name,num")
                  ->from($table)
                  ->where(array("wid"=>$id))
                  ->get();

        $result = $query->result_array();
        return $result;

    }

    //从day_report_1表中获得浏览量汇总
    public function getTotalView($id,$where=false){

        $table = 'day_report_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }

        $this->db2->select("sum(vcount) as total_view,sum(pnum) as total_vister")
                  ->from($table);

        if ($where) {
            $this->db2->where($where);         
        }
                  
        $query = $this->db2->get();
        $result = $query->row_array();
        return $result;

    }


    public function getUserInfoForm($id,$limit=false,$start=false){

        $table = 'm_user_info';
        $this->db2->select("name,mphone")
                  ->from($table)
                  ->where(array("wid"=>$id));

        if ($limit) {

            $this->db2->limit($limit,$start);
        }
                  
        $query= $this->db2->get();
        $result = $query->result_array();

        return $result;
    }

    public function getUserInfoFormCountAll($id){

        $table = 'm_user_info';
        $where = array("wid"=>$id);
        $count  = $this->db2->where($where)->count_all_results($table);

        return $count;
    }


    public function getRelationNum($id,$where=FALSE){

        $table = 'm_relation_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }

        $count = 0;
        if ($where === FALSE) {
            $count  = $this->db2->count_all_results($table);
        }else{
            $count  = $this->db2->where($where)->count_all_results($table);
        }
        return $count;
    }

    public function getHourReport($id){

        $table = 'hour_report_'.$id;
        if (!$this->db2->table_exists($table)) {

            return false;
        }
        $fields = array();
        $fields[] = 'sum(vcount) as uv_all';
        $fields[] = "hour(dcode) as date";
        $query = $this->db2->select($fields)
                  ->from($table)
                  ->group_by(array('date'))
                  ->get();
        return $query->result_array();
    }
}
